from tortoise import BaseDBAsyncClient


async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        CREATE TABLE IF NOT EXISTS `for_test` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `create_time` DATETIME(6) NOT NULL  COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `update_time` DATETIME(6) NOT NULL  COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `version` INT NOT NULL  DEFAULT 0,
    `test_1` INT,
    `test_2` VARCHAR(256),
    `test_3` VARCHAR(256),
    `test_4` VARCHAR(256),
    `test_5` VARCHAR(256),
    `test_6` VARCHAR(256)
) CHARACTER SET utf8mb4 COMMENT='for test';
CREATE TABLE IF NOT EXISTS `operate_log` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `create_time` DATETIME(6) NOT NULL  COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `update_time` DATETIME(6) NOT NULL  COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `version` INT NOT NULL  DEFAULT 0,
    `user_id` INT NOT NULL  COMMENT '用户ID',
    `target_url` VARCHAR(255)   COMMENT '访问的url',
    `user_agent` VARCHAR(255)   COMMENT '访问UA',
    `request_params` JSON   COMMENT '请求参数get|post',
    `ip` VARCHAR(32)   COMMENT '访问IP',
    `note` VARCHAR(255)   COMMENT '备注',
    `sql` LONGTEXT   COMMENT 'SQL语句'
) CHARACTER SET utf8mb4 COMMENT='用户操作记录表';
CREATE TABLE IF NOT EXISTS `family` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `create_time` DATETIME(6) NOT NULL  COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `update_time` DATETIME(6) NOT NULL  COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `version` INT NOT NULL  DEFAULT 0,
    `family_id` CHAR(36) NOT NULL UNIQUE COMMENT '家族唯一标识',
    `password` VARCHAR(255),
    `family_name` VARCHAR(50) NOT NULL  COMMENT '家族名称',
    `family_desc` VARCHAR(255)   COMMENT '家族描述',
    `family_status` INT NOT NULL  COMMENT '家族状态: 0-解散 1-正常' DEFAULT 1
) CHARACTER SET utf8mb4 COMMENT='家族表';
CREATE TABLE IF NOT EXISTS `permission` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `create_time` DATETIME(6) NOT NULL  COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `update_time` DATETIME(6) NOT NULL  COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `version` INT NOT NULL  DEFAULT 0,
    `permission_name` VARCHAR(32) NOT NULL UNIQUE COMMENT '权限名称',
    `permission_desc` VARCHAR(255)   COMMENT '权限描述'
) CHARACTER SET utf8mb4 COMMENT='权限表';
CREATE TABLE IF NOT EXISTS `role` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `create_time` DATETIME(6) NOT NULL  COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `update_time` DATETIME(6) NOT NULL  COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `version` INT NOT NULL  DEFAULT 0,
    `role_name` VARCHAR(32) NOT NULL UNIQUE COMMENT '角色名称',
    `role_status` BOOL NOT NULL  COMMENT 'True:启用 False:禁用' DEFAULT 0,
    `role_desc` VARCHAR(255)   COMMENT '角色描述'
) CHARACTER SET utf8mb4 COMMENT='角色表';
CREATE TABLE IF NOT EXISTS `user` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `create_time` DATETIME(6) NOT NULL  COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `update_time` DATETIME(6) NOT NULL  COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `version` INT NOT NULL  DEFAULT 0,
    `username` VARCHAR(64) NOT NULL UNIQUE COMMENT '用户名',
    `password` VARCHAR(255) NOT NULL,
    `user_phone` VARCHAR(11)   COMMENT '手机号',
    `user_email` VARCHAR(255)   COMMENT '邮箱',
    `full_name` VARCHAR(16)   COMMENT '姓名',
    `birthday` DATE NOT NULL  COMMENT '生日' DEFAULT '1970-01-01',
    `sex` INT NOT NULL  COMMENT '0未知 1男 2女' DEFAULT 0,
    `user_status` INT NOT NULL  COMMENT '0未激活 1正常 2禁用' DEFAULT 0,
    `header_img` VARCHAR(255)   COMMENT '头像',
    `remarks` VARCHAR(30)   COMMENT '备注',
    `family_id` INT,
    CONSTRAINT `fk_user_family_38f50400` FOREIGN KEY (`family_id`) REFERENCES `family` (`id`) ON DELETE SET NULL
) CHARACTER SET utf8mb4 COMMENT='用户表';
CREATE TABLE IF NOT EXISTS `user_relation` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `create_time` DATETIME(6) NOT NULL  COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `update_time` DATETIME(6) NOT NULL  COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `version` INT NOT NULL  DEFAULT 0,
    `relation_type` INT NOT NULL  COMMENT '关系类型: 1-父子关系 2-母子关系 3-夫妻关系',
    `remarks` VARCHAR(255)   COMMENT '关系备注',
    `child_id` INT NOT NULL,
    `family_id` INT,
    `parent_id` INT NOT NULL,
    CONSTRAINT `fk_user_rel_user_8966592f` FOREIGN KEY (`child_id`) REFERENCES `user` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_user_rel_family_f8052008` FOREIGN KEY (`family_id`) REFERENCES `family` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_user_rel_user_5c860339` FOREIGN KEY (`parent_id`) REFERENCES `user` (`id`) ON DELETE CASCADE,
    KEY `idx_user_relati_child_i_8aa479` (`child_id`, `relation_type`),
    KEY `idx_user_relati_parent__4200bc` (`parent_id`, `relation_type`)
) CHARACTER SET utf8mb4 COMMENT='用户关系表';
CREATE TABLE IF NOT EXISTS `user_wechat` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `create_time` DATETIME(6) NOT NULL  COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `update_time` DATETIME(6) NOT NULL  COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `version` INT NOT NULL  DEFAULT 0,
    `city` VARCHAR(255)   COMMENT '城市',
    `country` VARCHAR(255)   COMMENT '国家',
    `headimgurl` VARCHAR(255)   COMMENT '微信头像',
    `nickname` VARCHAR(255)   COMMENT '微信昵称',
    `openid` VARCHAR(255) NOT NULL UNIQUE COMMENT 'openid',
    `unionid` VARCHAR(255)  UNIQUE COMMENT 'unionid',
    `province` VARCHAR(255)   COMMENT '省份',
    `sex` INT   COMMENT '性别',
    `user_id` INT NOT NULL UNIQUE,
    CONSTRAINT `fk_user_wec_user_a1775abb` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='用户微信';
CREATE TABLE IF NOT EXISTS `aerich` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `version` VARCHAR(255) NOT NULL,
    `app` VARCHAR(100) NOT NULL,
    `content` JSON NOT NULL
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `role_permission` (
    `role_id` INT NOT NULL,
    `permission_id` INT NOT NULL,
    FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`permission_id`) REFERENCES `permission` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `user_role` (
    `user_id` INT NOT NULL,
    `role_id` INT NOT NULL,
    FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;"""


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        """
